import akshare as ak
from connector import mysqlConnectorHelper as mc
import datetime
import SimpleMySqlClass as smc


# 获取数据库链接
def get_mysql_conn():
    conn = smc.SimpleMySqlClass("127.0.0.1", "stocks", "haojg", "haojg")
    return conn


def get_data_and_read(code):
    # 拉取远端数据
    stock_qfq_data = get_stock_data(code)
    # 获取链接
    conn = get_mysql_conn()
    # 落库
    save_data_to_mysql(conn, code, stock_qfq_data)
    # 读数
    read_data_from_mysql(conn)
    # 关闭连接
    conn.close()


# 1, get akshare stock data
def get_stock_data(code):
    stock_qfq_data = []
    # stock_qfq_data = ak.stock_zh_a_hist(
    #     symbol=code, adjust="qfq", start_date="20230101", end_date="20231230"
    # )
    #
    # stock_qfq_data = ak.fund_etf_hist_em(
    #     symbol=code,
    #     period="daily",
    #     start_date="20230101",
    #     end_date="20231230",
    #     adjust="qfq",
    # )
    # stock_qfq_data = stock_qfq_data.iloc[:, 0:7]
    # print(stock_qfq_data)
    # 日期     开盘     收盘     最高     最低     成交量  成交额
    # 列名记得这样定义好
    # stock_qfq_data.columns = [
    #     "date",
    #     "open",
    #     "close",
    #     "high",
    #     "low",
    #     "volume",
    #     "amount",
    # ]
    # print("day data = ", stock_qfq_data)
    fund_etf_hist_min_em_df = ak.fund_etf_hist_min_em(
        symbol="510300",
        period="60",
        adjust="",
        start_date="20230101",
        end_date="20231230",
    )
    print("minute data", fund_etf_hist_min_em_df)

    fund_etf_hist_min_em_df = ak.fund_etf_hist_min_em(
        symbol="513500",
        start_date="2023-09-01 09:32:00",
        end_date="2024-01-01 09:32:00",
        period="30",
        adjust="",
    )

    print("minute data", fund_etf_hist_min_em_df)

    fund_etf_hist_min_em_df = ak.fund_etf_hist_min_em(
        symbol="510300",
        period="30",
        adjust="hfq",
        start_date="2023-12-11 09:00:00",
        end_date="2023-12-11 17:40:00",
    )
    print(fund_etf_hist_min_em_df)
    return stock_qfq_data


# 2, save data to mysql
def save_data_to_mysql(conn, code, stock_data_df):

    for index, row in stock_data_df.iterrows():
        # 插入新记录
        data = {
            "id": 0,
            "sd_date": row["date"],
            "sd_code": code,
            "sd_open": row["open"],
            "sd_high": row["high"],
            "sd_low": row["low"],
            "sd_close": row["close"],
            "sd_volume": row["volume"],
            "sd_amount": row["amount"],
            "create_time": datetime.datetime.now(),
        }
        # rs = conn.insert("t_stock_k_data", data)
        # print("rs = ", rs)

        table = "t_stock_k_data"
        # columns = ", ".join(data.keys())
        # placeholders = ", ".join(["%s"] * len(data))
        # sql = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        # args = (tuple(data.values()),)
        # rs = conn.executeMany(sql, args)
        # rs = conn.insert(table, data)
        # print("rs = ", rs)

    print("-------------------------------")
    return


# 3, query data from mysql
def read_data_from_mysql(conn):
    tableName = "t_stock_k_data"
    # sql = f"select sd_date, sd_code, sd_open, sd_high, sd_low, sd_close, sd_volume, sd_amount from {tableName}"
    sql = f"select count(*) from {tableName}"
    rs = conn.get_one(sql)
    print("query rs = ", rs)
    pass


if __name__ == "__main__":
    code = "510300"
    get_stock_data(code)
    # get_data_and_read(code)
    print("main")
